var express = require('express')
var router = express.Router()

//导入数据库操作模块
var exec = require('../db')
//导入调试的标记
const {
  debug
} = require('../config')

// 查询
router.get('/', async function (req, res) {
  // 搜索多条数据时, 显示的字段:
  let select_fields =
    'job_tb.id id,title,cover_picture,created_time,edited_time,category_id,visit_count,is_recommend,category_name,user_id,address,money,desprition,conpanyName'
  //声明sql语句变量
  let sql
  // 获取全部数据列表 GET /articles
  if (JSON.stringify(req.query) == '{}') {
    sql = `SELECT ${select_fields} FROM job_tb,category_tb where job_tb.category_id=category_tb.id && deleted_time is null`
  }
  // 获取推荐数据列表 GET /articles?is_recommend=1
  else if (req.query.is_recommend == 1) {
    sql = `SELECT ${select_fields} FROM job_tb,category_tb where job_tb.category_id=category_tb.id && is_recommend = 1 && deleted_time is null`
  }
  // 根据作者id获取数据 GET /articles?user_id=1
  else if (req.query.user_id == 1) {
    sql = `SELECT ${select_fields} FROM job_tb,category_tb where job_tb.category_id=category_tb.id && is_recommend = 1 && deleted_time is null`
  }
  // 根据标题模糊查询获取数据列表 GET /articles?title=总结
  else if (req.query.title) {
    sql = `SELECT ${select_fields} FROM job_tb job_tb,category_tb where job_tb.category_id=category_tb.id && title like '%${req.query.title}%' && deleted_time is null`
  }
  // 处理数据推荐申请
  else if (req.query.apply_recommend == 1) {
    sql = `SELECT ${select_fields},content FROM job_tb,category_tb where job_tb.apply_recommend = 1 && job_tb.category_id=category_tb.id  && deleted_time is null `
  }
  // 根据分类获取数据列表 GET /articles?categorys_id=1
  else if (req.query.category_id) {
    // sql = `SELECT ${select_fields} FROM job_tb,category_tb where job_tb.category_id=category_tb.id && category_id = ${req.query.category_id} && deleted_time is null`
    sql = `select 
    c.id, title,cover_picture,created_time,category_id,
    visit_count, is_recommend,
    comments_num,
    category_name,
    address, money, desprition, conpanyName
    from 
    (	select a.*, ifnull(b.num, 0) comments_num 
      from (select * from job_tb where category_id = ${req.query.category_id} && deleted_time is null) a 
      left join 
      (select article_id, count(*) num from comment_tb where approved=1 GROUP BY article_id) b 
      on id=article_id 
    ) c, category_tb 
    where c.category_id=category_tb.id
    ORDER BY created_time desc`
  }
  // 根据数据id获取单篇数据 GET /articles?id=2
  else if (req.query.id) {
    sql = `SELECT ${select_fields},content FROM job_tb,category_tb where job_tb.id = ${req.query.id} && job_tb.category_id=category_tb.id  && deleted_time is null `
  }

  // 根据作者id获取数据 GET /articles?user_id=2
  else if (req.query.user_id) {
    sql = `SELECT ${select_fields},content FROM job_tb,category_tb where job_tb.user_id = ${req.query.user_id} && job_tb.category_id=category_tb.id  && deleted_time is null `
  }

  // 根据时间轴 获取某一月份的数据 GET /articles?year=2022&month=10
  else if (req.query.year && req.query.month) {
    const {
      year,
      month
    } = req.query
    // sql = `SELECT ${select_fields} FROM job_tb,category_tb where job_tb.category_id=category_tb.id && YEAR(created_time)=${year} && MONTH(created_time)=${month} && deleted_time is null`
    sql = `select 
    c.id, title,cover_picture,created_time,category_id,
    visit_count, is_recommend,
    comments_num,
    category_name
    from 
    (	select a.*, ifnull(b.num, 0) comments_num 
      from (select * from job_tb where YEAR(created_time)=${year} && MONTH(created_time)=${month} && deleted_time is null) a 
      left join 
      (select article_id, count(*) num from comment_tb where approved=1 GROUP BY article_id) b 
      on id=article_id 
    ) c, category_tb 
    where c.category_id=category_tb.id
    ORDER BY created_time desc
  `
  }
  // 获取数据表中的月份 GET /articles?type=month
  else if (req.query.type == 'month') {
    sql = `SELECT distinct YEAR(created_time) year, MONTH(created_time) month FROM job_tb where deleted_time is null`
  }
  // 查询每个分类对应的数据记录数 GET /articles?type=category_stat
  else if (req.query.type == 'category_stat') {

    sql = `SELECT category_id, category_name category, count(*) count FROM job_tb,category_tb where job_tb.category_id=category_tb.id  && deleted_time is null GROUP BY category_id, category_name`
  }
  // 查询每个月份对应的数据记录数 GET /articles?type=month_stat
  else if (req.query.type == 'month_stat') {
    sql = `SELECT YEAR(created_time) year, MONTH(created_time) month, count(*) count FROM job_tb  where deleted_time is null group by YEAR(created_time),MONTH(created_time)`
  }
  // 分页查询数据 GET /articles?page=1&size=5
  else if (req.query.page && req.query.size) {
    const {
      page,
      size
    } = req.query
    let sql = `select count(*) as total from job_tb where deleted_time is null`
    let {
      total
    } = (await exec(sql))[0] // { total: 9 }

    sql = `select 
    c.id, title,cover_picture,created_time,edited_time,category_id,
    visit_count, is_recommend,
    comments_num,
    category_name,
    address, money, desprition, conpanyName
    from 
    (	select a.*, ifnull(b.num, 0) comments_num 
      from (select * from job_tb where deleted_time is null) a 
      left join 
      (select article_id, count(*) num from comment_tb where approved=1 GROUP BY article_id) b 
      on id=article_id 
    ) c, category_tb 
    where c.category_id=category_tb.id
    ORDER BY created_time desc
    limit ${size * (page - 1)},${size}`

    // 数据响应
    try {
      res.send({
        code: 0,
        msg: '查询成功',
        result: {
          total,
          articles: await exec(sql)
        }
      })
      return
    } catch (err) {
      res.send({
        code: 1,
        msg: '失败',
        result: debug ? err : ''
      })
      return
    }
  }

  // 数据响应
  try {
    res.send({
      code: 0,
      msg: '查询成功',
      result: await exec(sql)
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '失败',
      result: debug ? err : ''
    })
  }
})

// 增加数据 post /articles
router.post('/', async (req, res) => {
  let {
    title,
    cover_picture,
    content,
    category_id,
    is_recommend,
    address,
    money,
    desprition,
    conpanyName
  } = req.body
  // 如果未上传封面, 给一个默认封面
  if (!cover_picture) {
    // 包含 cover_picture=='', null
    cover_picture = 'http://localhost:3000/download/coverPicture/?img=default'
  }
  //新增语句
  // let sql = `INSERT INTO job_tb (title, cover_picture, description, content, category_id, is_recommend, apply_recommend) VALUES ('${title}', '${cover_picture}', '${description}', '${content}', ${category_id}, ${
  //   is_recommend ? is_recommend : 0
  // },${apply_recommend})`

  let sql = `INSERT INTO job_tb (title, cover_picture, content, category_id, is_recommend,address,money,desprition,conpanyName) VALUES ('${title}', '${cover_picture}', '${content}', ${category_id}, ${
    is_recommend ? is_recommend : 0
  }, '${address}', '${money}', '${desprition}', '${conpanyName}')
  `
  try {
    //返回
    const data = await exec(sql)
    res.send({
      code: 0,
      msg: '添加成功',
      result: data.insertId
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '添加失败',
      result: debug ? err : ''
    })
  }
})

// 根据id修改博客内容 PUT /articles?id=2
// 根据id修改博客是否推荐 PUT /articles?id=2
router.put('/', async (req, res) => {
  // 获取请求数据
  let body = req.body
  // 过滤需要修改的数据
  let bodyfilter = ''
  for (let key in body) {
    if (body[key] !== '') {
      bodyfilter += `${key}='${body[key]}',`
    }
  }
  //删除句尾逗号
  bodyfilter = bodyfilter.slice(0, bodyfilter.length - 1)

  //修改内容
  let sql = `UPDATE job_tb SET ${bodyfilter} where id = ${req.query.id}`

  try {
    let data = await exec(sql)
    res.send({
      code: 0,
      msg: '修改成功',
      result: ''
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '修改失败',
      result: debug ? err : ''
    })
  }
})
// 根据id为数据添加一个访问量 PUT /articles?id=2
router.put('/view_num', async (req, res) => {
  //修改内容
  let sql = `UPDATE job_tb SET visit_count = visit_count+1 where id = ${req.query.id}`

  try {
    let data = await exec(sql)
    res.send({
      code: 0,
      msg: '修改成功',
      result: ''
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '修改失败',
      result: debug ? err : ''
    })
  }
})

// 软删除 DELETE /articles?id=1
router.delete('/:id', async (req, res) => {
  sql = `UPDATE job_tb SET deleted_time = NOW() where id = ${req.params.id}`

  //响应
  try {
    let data = await exec(sql)
    res.send({
      code: 0,
      msg: '删除成功',
      result: ''
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '删除失败',
      result: debug ? err : ''
    })
  }
})

module.exports = router